﻿using System;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient;
using Common.SQL;
using System.Reflection;
using System.Collections.Generic;
namespace MRS.DBL.DAL
{
    /// <summary>
    /// 数据访问类:UnitDAL
    /// </summary>
    public partial class UnitDAL
    {
        Type _oType = System.Reflection.MethodBase.GetCurrentMethod().DeclaringType;

        log4net.ILog _logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        #region  Method
        /// <summary>
        /// 是否存在该名称或者code
        /// </summary>
        public bool Exists(string unitName,string unitCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from unit");
            strSql.Append(" where unitName=@unitName or unitCode=@unitCode");
            MySqlParameter[] parameters = {
					new MySqlParameter("@unitName", MySqlDbType.VarChar),
                    new MySqlParameter("@unitCode", MySqlDbType.VarChar)
			};
            parameters[0].Value = unitName;
            int effectedrows = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                effectedrows = Convert.ToInt32(SQLHelper.ExecuteScalar(conn, CommandType.Text, strSql.ToString(), parameters));//返回最新添加的ID
            }
            catch (Exception ex)
            {
                _logger.ErrorFormat("{0}.{1}.{2}: {3}", MethodBase.GetCurrentMethod().DeclaringType.Namespace, MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }
            return effectedrows>0;
        }
        
        /// <summary>
        /// 更新单位信息时检测是否已存在单位名称或者单位编码
        /// </summary>
        /// <param name="unitId"></param>
        /// <param name="unitName"></param>
        /// <param name="unitCode"></param>
        /// <returns></returns>
        public bool ExistsUnitNameOrCodeWhenUpdate(int unitId,string unitName,string unitCode) 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from unit");
            strSql.Append(" where (unitCode=@unitCode or unitName=@unitName) and unitId<>@unitId");
            MySqlParameter[] parameters = {
					new MySqlParameter("@unitCode", MySqlDbType.VarChar),
                    new MySqlParameter("@unitName", MySqlDbType.VarChar),
                    new MySqlParameter("@unitId", MySqlDbType.Int32)
			};
            parameters[0].Value = unitCode;
            parameters[1].Value = unitName;
            parameters[2].Value = unitId;
            int effectedrows = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                effectedrows = Convert.ToInt32(SQLHelper.ExecuteScalar(conn, CommandType.Text, strSql.ToString(), parameters));//返回最新添加的ID
            }
            catch (Exception ex)
            {
                _logger.ErrorFormat("{0}.{1}.{2}: {3}", MethodBase.GetCurrentMethod().DeclaringType.Namespace, MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }
            return effectedrows > 0;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(MRS.DBL.Model.UnitModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into unit(");
            strSql.Append("UnitName,UnitCode,Enable,CreateDate,ExpireDate,Industry)");
            strSql.Append(" values (");
            strSql.Append("@UnitName,@UnitCode,@Enable,@CreateDate,@ExpireDate,@Industry);");
            strSql.Append("select @@IDENTITY;");
            MySqlParameter[] parameters = {
					new MySqlParameter("@UnitName", MySqlDbType.VarChar,50),
					new MySqlParameter("@UnitCode", MySqlDbType.VarChar,10),
					new MySqlParameter("@Enable", MySqlDbType.VarChar,1),
					new MySqlParameter("@CreateDate", MySqlDbType.Date),
					new MySqlParameter("@ExpireDate", MySqlDbType.Date),
					new MySqlParameter("@Industry", MySqlDbType.Int32,11)};
            parameters[0].Value = model.UnitName;
            parameters[1].Value = model.UnitCode;
            parameters[2].Value = model.Enable;
            parameters[3].Value = model.CreateDate;
            parameters[4].Value = model.ExpireDate;
            parameters[5].Value = model.Industry;

            int effectedrows = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                effectedrows = Convert.ToInt32(SQLHelper.ExecuteScalar(conn, CommandType.Text, strSql.ToString(), parameters));//返回最新添加的ID
            }
            catch (Exception ex)
            {
                _logger.ErrorFormat("{0}.{1}.{2}: {3}", MethodBase.GetCurrentMethod().DeclaringType.Namespace, MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }
            return effectedrows;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(MRS.DBL.Model.UnitModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update unit set ");
            strSql.Append("UnitName=@UnitName,");
            strSql.Append("UnitCode=@UnitCode,");
            strSql.Append("Enable=@Enable,");
            strSql.Append("CreateDate=@CreateDate,");
            strSql.Append("ExpireDate=@ExpireDate,");
            strSql.Append("Industry=@Industry");
            strSql.Append(" where UnitId=@UnitId");
            MySqlParameter[] parameters = {
					new MySqlParameter("@UnitName", MySqlDbType.VarChar,50),
					new MySqlParameter("@UnitCode", MySqlDbType.VarChar,10),
					new MySqlParameter("@Enable", MySqlDbType.VarChar,1),
					new MySqlParameter("@CreateDate", MySqlDbType.Date),
					new MySqlParameter("@ExpireDate", MySqlDbType.Date),
					new MySqlParameter("@Industry", MySqlDbType.Int32,11),
					new MySqlParameter("@UnitId", MySqlDbType.Int32,11)};
            parameters[0].Value = model.UnitName;
            parameters[1].Value = model.UnitCode;
            parameters[2].Value = model.Enable;
            parameters[3].Value = model.CreateDate;
            parameters[4].Value = model.ExpireDate;
            parameters[5].Value = model.Industry;
            parameters[6].Value = model.UnitId;
            int effectedrows = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSql.ToString(), parameters);
            }
            catch (Exception ex)
            {
                _logger.ErrorFormat("{0}.{1}.{2}: {3}", MethodBase.GetCurrentMethod().DeclaringType.Namespace, MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }
            return effectedrows > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int UnitId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from unit ");
            strSql.Append(" where UnitId=@UnitId");
            MySqlParameter[] parameters = {
					new MySqlParameter("@UnitId", MySqlDbType.Int32)
			};
            parameters[0].Value = UnitId;

            int effectedrows = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSql.ToString(), parameters);
            }
            catch (Exception ex)
            {
                _logger.ErrorFormat("{0}.{1}.{2}: {3}", MethodBase.GetCurrentMethod().DeclaringType.Namespace, MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }
            return effectedrows > 0;
        }
        /// <summary>
        /// 批量删除数据 UnitId 之间以逗号(,)隔开
        /// </summary>
        public bool DeleteList(string UnitIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from unit ");
            strSql.Append(" where UnitId in (" + UnitIdlist + ")  ");
            int effectedrows = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSql.ToString(), null);
            }
            catch (Exception ex)
            {
                _logger.ErrorFormat("{0}.{1}.{2}: {3}", MethodBase.GetCurrentMethod().DeclaringType.Namespace, MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }
            return effectedrows > 0;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public MRS.DBL.Model.UnitModel GetModel(int UnitId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UnitId,UnitName,UnitCode,Enable,CreateDate,ExpireDate,Industry from unit ");
            strSql.Append(" where UnitId=@UnitId");
            MySqlParameter[] parameters = {
					new MySqlParameter("@UnitId", MySqlDbType.Int32)
			};
            parameters[0].Value = UnitId;

            MRS.DBL.Model.UnitModel model = new MRS.DBL.Model.UnitModel();
            
            DataSet ds = null;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                ds = SQLHelper.QueryDataSet(conn, CommandType.Text, strSql.ToString(), parameters);
            }
            catch (Exception ex)
            {
                _logger.ErrorFormat("{0}.{1}.{2}: {3}", MethodBase.GetCurrentMethod().DeclaringType.Namespace, MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            if (ds == null)
                return null;
            DBL.Model.UnitModel[] unitList = GetUnitInfoFromDataTable(ds.Tables[0]);
            if (unitList == null || unitList.Length == 0)
                return null;
            return unitList[0];
        }

        public DBL.Model.UnitModel[] GetUnitList() 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UnitId,UnitName,UnitCode,Enable,CreateDate,ExpireDate,Industry from unit ");

            MRS.DBL.Model.UnitModel model = new MRS.DBL.Model.UnitModel();

            DataSet ds = null;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                ds = SQLHelper.QueryDataSet(conn, CommandType.Text, strSql.ToString(), null);
            }
            catch (Exception ex)
            {
                _logger.ErrorFormat("{0}.{1}.{2}: {3}", MethodBase.GetCurrentMethod().DeclaringType.Namespace, MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            if (ds == null)
                return null;
            DBL.Model.UnitModel[] unitList = GetUnitInfoFromDataTable(ds.Tables[0]);
            if (unitList == null || unitList.Length == 0)
                return null;
            return unitList;
        }
              

        /// <summary>
        /// 从DataTable中获取数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private DBL.Model.UnitModel[] GetUnitInfoFromDataTable(DataTable dt) 
        {
            if (dt == null || dt.Rows.Count <= 0)
                return null;

            List<DBL.Model.UnitModel> unitList = new List<Model.UnitModel>();
            DBL.Model.UnitModel model = null;
            for (int i = 0; i < dt.Rows.Count; i++) 
            {
                model = new Model.UnitModel();

                if (dt.Rows[i]["UnitId"] != null && dt.Rows[i]["UnitId"].ToString() != "")
                {
                    model.UnitId = int.Parse(dt.Rows[i]["UnitId"].ToString());
                }
                if (dt.Rows[i]["UnitName"] != null && dt.Rows[i]["UnitName"].ToString() != "")
                {
                    model.UnitName = dt.Rows[i]["UnitName"].ToString();
                }
                if (dt.Rows[i]["UnitCode"] != null && dt.Rows[i]["UnitCode"].ToString() != "")
                {
                    model.UnitCode = dt.Rows[i]["UnitCode"].ToString();
                }
                if (dt.Rows[i]["Enable"] != null && dt.Rows[i]["Enable"].ToString() != "")
                {
                    model.Enable = dt.Rows[i]["Enable"].ToString();
                }
                if (dt.Rows[i]["CreateDate"] != null && dt.Rows[i]["CreateDate"].ToString() != "")
                {
                    model.CreateDate = DateTime.Parse(dt.Rows[i]["CreateDate"].ToString());
                }
                if (dt.Rows[i]["ExpireDate"] != null && dt.Rows[i]["ExpireDate"].ToString() != "")
                {
                    model.ExpireDate = DateTime.Parse(dt.Rows[i]["ExpireDate"].ToString());
                }
                if (dt.Rows[i]["Industry"] != null && dt.Rows[i]["Industry"].ToString() != "")
                {
                    model.Industry = int.Parse(dt.Rows[i]["Industry"].ToString());
                }
                unitList.Add(model);
            }
            return unitList.ToArray();
        }

        #endregion  Method
    }
}

